<?php

namespace app\admin\controller;

use app\common\model\Tags;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use think\facade\App;
use think\facade\Db;
use Throwable;
use app\common\controller\Backend;

/**
 * 找店管理
 */
class Findstore extends Backend
{
    /**
     * Findstore模型对象
     * @var object
     * @phpstan-var \app\common\model\Findstore
     */
    protected object $model;

    protected array|string $preExcludeFields = ['id', 'create_time'];

    protected array $withJoinTable = ['user'];

    protected string|array $quickSearchField = ['id'];

    public function initialize(): void
    {
        parent::initialize();
        $this->model = new \app\common\model\Findstore;
    }

    /**
     * 查看
     * @throws Throwable
     */
    public function index(): void
    {
        // 如果是 select 则转发到 select 方法，若未重写该方法，其实还是继续执行 index
        if ($this->request->param('select')) {
            $this->select();
        }

        /**
         * 1. withJoin 不可使用 alias 方法设置表别名，别名将自动使用关联模型名称（小写下划线命名规则）
         * 2. 以下的别名设置了主表别名，同时便于拼接查询参数等
         * 3. paginate 数据集可使用链式操作 each(function($item, $key) {}) 遍历处理
         */
        list($where, $alias, $limit, $order) = $this->queryBuilder();
        $res = $this->model
            ->withJoin($this->withJoinTable, $this->withJoinType)
            ->alias($alias)
            ->where($where)
            ->order($order)
            ->paginate($limit);
        $res->visible(['user' => ['username','nickname','mobile']]);

        $this->success('', [
            'list'   => $res->items(),
            'total'  => $res->total(),
            'remark' => get_route_remark(),
        ]);
    }

    /**
     * 若需重写查看、编辑、删除等方法，请复制 @see \app\admin\library\traits\Backend 中对应的方法至此进行重写
     */

    public function import(): void
    {
        set_time_limit(0);
        ob_clean();
        $file = $this->request->post('file');
        if (empty($file)) {
            $this->error('请选择上传的文件');
        }
        $ext = pathinfo($file, PATHINFO_EXTENSION);//后缀
        if (!in_array($ext, ['xls', 'xlsx'])) {
            $this->error('不支持此类型文件');
        }
        if ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }
        $filePath = App::getRootPath() . 'public' . DIRECTORY_SEPARATOR . $file;
        $data = [];
        $reader->setReadDataOnly(TRUE);
        $spreadsheet = $reader->load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $highestRow = $sheet->getHighestRow();
        $allColumn = $sheet->getHighestDataColumn(); //取得最大的列号
        $allRow = $sheet->getHighestRow(); //取得一共有多少行
        $user_id =$this->request->post('user_id');
        if(empty($user_id)){
            $this->error('请选择用户');
        }
        $highestColumnIndex = Coordinate::columnIndexFromString($allColumn);//最大列转换成数字
        $fileds = [
            '',
            'industry_pid',
            'industry',
            'city',
            'user_id',
            'contact_person',
            'start_area',
            'start_rent',
            'rent_unit',
            'reward',
        ];
        $model = new  \app\common\model\Findstore();
        $field_count = count($fileds);
        for ($row = 2; $row <= $highestRow; $row++) {
            // 构建默认字段数据
            $arr_value = [
                'create_time' => time(),
                'update_time' => time(),
            ];
            for ($col = 1; $col <= $field_count - 1; $col++) {
                $chr = $this->getColumnZimu($col);
                // 列转为数据库字段名
                $key = $fileds[$col] ?? $chr;
                $value = $sheet->getCell($chr . $row)->getValue();
                if (in_array($key, ['industry_pid','industry']) && !empty($value)) {
                    $speal_arr = array('；', ';','，','、');
                    foreach ($speal_arr as $speal) {
                        $value = str_replace($speal, ',', $value);
                    }
                    $cate_values = explode(',', $value);
                    $cate_ids = (new Tags())->whereIn('string', $cate_values)->column('id');
                    $value = $cate_ids ? implode(',', $cate_ids) : '';
                }
                if($key=='city'){
                    $value=str_replace(',',',',$value);
                    $value=str_replace('，',',',$value);
                    $value=str_replace('；',',',$value);
                    $value=str_replace(';',',',$value);
                    $value=Db::name('area')->whereIn('name',$value)->column('id');
                    $value=$value?implode(',',$value):'';
                }
                $value = trim($value);
                $arr_value[$key] = $value;
            }
            $arr_value['user_id'] =$user_id;
            $arr_value['name'] ='导入店';
            $data[] = $arr_value;
        }
        $result = $model->saveAll($data);
        if ($result) {
            $this->success('导入成功' . count($data) . '条数据', $data);
        } else {
            $this->error('导入失败', $data);
        }
    }
    private function getColumnZimu($num)
    {
        if ($num >= 0 && $num < 26) {
            // 如果是 A 到 Z 之间的列，直接返回对应的字母
            return chr(65 + $num);
        } else {
            // 针对 AA、AB、AC ... ZZ 这样的列，使用类似递归的方式计算出对应的字母组合
            $result = '';
            while ($num >= 26) {
                $result .= chr(65 + ($num % 26));
                $num = intval($num / 26) - 1;
            }
            $result .= chr(65 + $num);
            return strrev($result); // 需要反转列名字母组合
        }
    }
}
